/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.business.repository;

import io.lettuce.core.dynamic.annotation.Param;
import me.zhengjie.business.domain.Person;
import me.zhengjie.business.domain.Zhsq;
import me.zhengjie.business.domain.vo.*;
import me.zhengjie.business.service.dto.GeneralQueryCriteria;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
* @website https://el-admin.vip
* @author xiahongbo
* @date 2022-04-15
**/
public interface ZhsqRepository extends JpaRepository<Zhsq, Long>, JpaSpecificationExecutor<Zhsq> {


    /**
     * 更改状态
     * @param zt 状态
     * @param id 主键
     */
    @Modifying
    @Query(value = "update bis_zhsq set bis_zhsq_zt = ?1 where bis_zhsq_id = ?2",nativeQuery = true)
    void updateZhzt(String zt, Long id);
    @Modifying
    @Query(value = "update bis_zhsq set bis_zhsq_zt = ?1, BIS_ZHSQ_DYJSRQ = to_date(to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd') where bis_zhsq_id = ?2",nativeQuery = true)
    void updateZhzt1(String zt, Long id);


    @Modifying
    @Query(value = "update bis_zhsq set bis_zhsq_tjsj = to_date(?1, 'yyyy-mm-dd'), bis_zhsq_dykkrq = to_date(?1, 'yyyy-mm-dd') where bis_zhsq_id = ?2",nativeQuery = true)
    void update(String tjrq, Long id);
    /**
     * 作废
     * @param cause 原因
     * @param id 主键
     */
    @Modifying
    @Query(value = "update bis_zhsq set bis_zhsq_zt = '91', bz = ?1 where bis_zhsq_id = ?2",nativeQuery = true)
    void cancelZhsq(String cause, Long id);
    @Modifying
    @Query(value = "update bis_zhsq set bis_zhsq_rybh = ?1 where bis_zhsq_id = ?2",nativeQuery = true)
    void updateSpbh(String spbh, Long id);
    /**
     * 清空商保公司，评估机构
     * @param id 主键
     */
    @Modifying
    @Query(value = "update bis_zhsq set BIS_SBGS_ID = '',BIS_PGGS_ID = '' where bis_zhsq_id = ?1",nativeQuery = true)
    void updateSbgsEmpty(Long id);


    @Modifying
    @Query(value = "update bis_zhsq set BIS_ZHSQ_SNRY_XM = ?1,BIS_ZHSQ_SNRY_SFZ = ?2, BIS_ZHSQ_HLDZ_SSQX=?3, BIS_ZHSQ_HLDZ_XXDZ=?4, " +
            "BIS_ZHSQ_HLDZ=?5, BIS_ZHSQ_LXDH=?6, BIS_ZHSQ_JHRDH=?7 where BIS_ZHSQ_SNRY_ID = ?8",nativeQuery = true)
    void updateJbxx(String xm, String sfz, String ssqx, String xxdz, String hldz, String lxdh, String jhrlxdh, String snryId);

    /**
     * 更改状态
     * @param zt 状态
     */
    @Modifying
    @Query(value = "update bis_zhsq set bis_zhsq_zt = ?1 where bis_zhsq_id IN ( ?2 )",nativeQuery = true)
    void batchUpdateZhzt(String zt, List<Long> ids);

    /**
     * 更改待遇结束日期
     * @param zzrq
     * @param id
     */
    @Modifying
    @Query(value = "update bis_zhsq set bis_zhsq_zt = '90', bis_zhsq_dyjsrq = to_date(?1, 'yyyy-mm-dd hh24:mi:ss') where bis_zhsq_id = ?2",nativeQuery = true)
    void updateDyjsrq(String zzrq, Long id);

    /**
     * 根据身份证查询
     * @param bisZhsqSnrySfz 身份证
     * @return /
     */
    List<Zhsq> findByBisZhsqSnrySfzOrderByBisZhsqId(String bisZhsqSnrySfz);


    @Query(value = "select *  " +
            "  from (select t.*, " +
            "  row_number() over(partition by bis_zhsq_snry_sfz order by bis_zhsq_dyjsrq desc) as rn " +
            "  from bis_zhsq t where t.bis_zhsq_zt in ('05','90') and t.bis_zhsq_zhlb='01' and t.bis_zhsq_dyjsrq is not null) " +
            " where rn=1   and bis_zhsq_snry_sfz=?1  " +
            " and bis_zhsq_sndj in ('中度失能','重度失能','重度失能Ⅰ级','重度失能Ⅱ级', '重度失能Ⅲ级') " +
            " and bis_zhsq_dyjsrq+1 >= to_date(?2, 'yyyy-mm-dd') ",nativeQuery = true)
    Zhsq querySnSqlb(String bisZhsqSnrySfz, String tjrq);

    @Query(value = "select *  " +
            "  from (select t.*, " +
            "  row_number() over(partition by bis_zhsq_snry_sfz order by bis_zhsq_dyjsrq desc) as rn " +
            "  from bis_zhsq t where t.bis_zhsq_zt in ('05','90') and t.bis_zhsq_zhlb='01' and t.bis_zhsq_dyjsrq is not null) " +
            " where rn=1   and bis_zhsq_snry_sfz=?1  " +
            " and bis_zhsq_sndj in ('85周岁以上','90周岁以上') " +
            " and bis_zhsq_dyjsrq+1 >= to_date(?2, 'yyyy-mm-dd') ",nativeQuery = true)
    Zhsq queryGlSqlb(String bisZhsqSnrySfz, String tjrq);

    @Query(value = "select *  " +
            "  from (select t.*, " +
            "  row_number() over(partition by bis_zhsq_snry_sfz order by bis_zhsq_dyjsrq desc) as rn " +
            "  from bis_zhsq t where t.bis_zhsq_zt in ('07','12','13','90') and t.bis_zhsq_zhlb='03' and t.bis_zhsq_dyjsrq is not null) " +
            " where rn=1   and bis_zhsq_snry_sfz=?1  " +
            " and bis_zhsq_sndj in ('重度失能','重度失能Ⅰ级','重度失能Ⅱ级', '重度失能Ⅲ级') " +
            " and bis_zhsq_dyjsrq+1 >= to_date(?2, 'yyyy-mm-dd') ",nativeQuery = true)
    Zhsq queryHomeSqlb(String bisZhsqSnrySfz, String tjrq);

    /**
     * 根据身份证和待遇日期查询zhid
     * @param bisZhsqSnrySfz /
     * @return /
     */
  //  @Query(value = "select * from BIS_ZHSQ t where t.bis_zhsq_snry_sfz = ?1 and t.bis_zhsq_dykkrq < sysdate and t.bis_zhsq_dyjsrq > sysdate",nativeQuery = true)
    @Query(value = "select * from BIS_ZHSQ t where t.bis_zhsq_snry_sfz = ?1 and t.bis_zhsq_zt='05' and t.bis_zhsq_zhlb='01' and t.bis_zhsq_hljg_bm= ?2 ",nativeQuery = true)
    Zhsq queryZhid(String bisZhsqSnrySfz, String hljgbm);

    /**
     * 根据身份证和待遇日期查询zhid
     * @param bisZhsqSnrySfz /
     * @return /
     */
    //  @Query(value = "select * from BIS_ZHSQ t where t.bis_zhsq_snry_sfz = ?1 and t.bis_zhsq_dykkrq < sysdate and t.bis_zhsq_dyjsrq > sysdate",nativeQuery = true)
    @Query(value = "select * from BIS_ZHSQ t where t.bis_zhsq_snry_sfz = ?1 and t.bis_zhsq_zt='05' and t.bis_zhsq_zhlb='01'",nativeQuery = true)
    Zhsq queryBySfz(String bisZhsqSnrySfz);


    @Query(value = "select BIS_ZHSQ_ID from BIS_ZHSQ t where t.BIS_USER_ID = ?1 and t.bis_zhsq_zt in ('13','90')",nativeQuery = true)
    List<Long> queryZhidsByUserId(Long userId);

    /**
     * 根据身份证查询，最近的一次自评审批是否超过180天
     * @param bisZhsqSnrySfz /
     * @return /
     */
    @Query(value = "select r.bis_zhsq_snry_xm,r.bis_zhsq_zt, " +
            "   to_char(r.bis_zhsq_dykkrq, 'yyyy-mm-dd'), to_char(t.lrsj, 'yyyy-mm-dd hh24:mi:ss'), " +
            "   to_char(sysdate - r.bis_zhsq_dykkrq), to_char(sysdate - t.lrsj)" +
            "  from BIS_ZHSQ_HISTORY t, " +
            "       (select * " +
            "          from (select * " +
            "                  from bis_zhsq t " +
            "                 where t.bis_zhsq_snry_sfz = ?1 and t.bis_zhsq_zhlb='03' " +
            "                 order by t.bis_zhsq_id desc) " +
            "         where rownum = 1) r " +
            " where t.result = '通过' " +
            "   and t.type = '02' " +
            "   and t.zhsq_id = r.bis_zhsq_id " +
            "   order by t.lrsj desc ",nativeQuery = true)
    List<Object> checkPgtg(String bisZhsqSnrySfz);


    @Query(value = "select to_char(t.lrsj, 'yyyy-mm-dd hh24:mi:ss'),  to_char(sysdate - t.lrsj), r.bis_zhsq_snry_xm " +
            "  from BIS_ZHSQ_HISTORY t, " +
            "       (select * " +
            "          from (select * " +
            "                  from bis_zhsq t " +
            "                 where t.bis_zhsq_snry_sfz = ?1 and t.bis_zhsq_zhlb='03' " +
            "                 order by t.bis_zhsq_id desc) " +
            "         where rownum = 1) r " +
            " where t.result = '未通过' " +
            "   and t.type = '02' " +
            "   and t.zhsq_id = r.bis_zhsq_id " +
            "   order by t.lrsj desc ", nativeQuery = true)
    List<Object> checkPgcs(String bisZhsqSnrySfz);


    @Query(value = "select to_char(t.lrsj, 'yyyy-mm-dd hh24:mi:ss'),  to_char(sysdate - t.lrsj), r.bis_zhsq_snry_xm, t.bz " +
            "  from BIS_ZHSQ_HISTORY t, " +
            "       (select * " +
            "          from (select * " +
            "                  from bis_zhsq t " +
            "                 where t.bis_zhsq_snry_sfz = ?1 and t.bis_zhsq_zhlb='03' " +
            "                 order by t.bis_zhsq_id desc) " +
            "         where rownum = 1) r " +
            " where t.result = '未通过' " +
            "   and t.type = '11' " +
            "   and t.zhsq_id = r.bis_zhsq_id " +
            "   order by t.lrsj desc ", nativeQuery = true)
    List<Object> checkZpsp(String bisZhsqSnrySfz);

    /**
     * 查询失能患者30内的申请次数
     * @param sfz /
     * @return /
     */
    @Query(value = "select count(1) cnt from BIS_ZHSQ t where TO_NUMBER(sysdate- t.lrsj) <= 30 and t.bis_zhsq_snry_sfz=?1 and t.bis_zhsq_zhlb='03'",nativeQuery = true)
    Integer queryApplyCount(String sfz);

    /**
     * 查询自评审批未通过的次数
     * @param zhid /
     * @return /
     */
    @Query(value = "select count(1) from BIS_ZHSQ_HISTORY t where t.result = '未通过' and t.type = '11' and t.zhsq_id = ?1 ",nativeQuery = true)
    Integer checkZpspWtg(Long zhid);

    /**
     * 查询昨天待遇过期的照护申请
     * @return /
     */
    @Query(value = "select to_char(wm_concat(z.bis_zhsq_id)) as IDS from bis_zhsq z where to_char(z.bis_zhsq_dyjsrq,'yyyymmdd') <= to_char(sysdate - 1,'yyyymmdd') and z.bis_zhsq_zt !='90'",nativeQuery = true)
    String queryGqdyzhsq();


    /**
     * 计算机构延期天数
     * @param zhid
     * @return
     */
    @Query(value = "select t.bis_zhsq_dyjsrq - to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') from BIS_ZHSQ t where t.bis_zhsq_id = ?1 ",nativeQuery = true)
    Integer calculateOrgDelayDays(Long zhid);

    /**
     * 获取评估日期
     * @return /
     */
    @Query(value = "select lrsj from " +
            "(select  to_char(t.lrsj, 'yyyy-mm-dd') lrsj  from BIS_ZHSQ_HISTORY t where t.zhsq_id=?1 and t.type='02' and t.result='通过' order by t.lrsj desc) " +
            "where rownum=1",nativeQuery = true)
    String getFpTargetDate(Long zhid);

    /**
     * 查询首页概况
     * @param gsid /
     * @return /
     */
    @Query(value = "select  " +
            // 已申请
            "(select count(1) from bis_zhsq t where (?1 IS NULL OR ?1 = '' OR t.BIS_SBGS_ID = ?1 ) and t.bis_zhsq_zhlb='03') ysq_cnt, " +
            // 评估成功
            "(select count(1) from bis_zhsq t1, bis_zhsq_history t2 where t1.bis_zhsq_id=t2.zhsq_id and t2.type='02' and t2.result='通过' and (?1 IS NULL OR ?1 = '' OR t1.BIS_SBGS_ID=?1 ) and t1.bis_zhsq_zhlb='03') pgcg_cnt, " +
            // 评估人次
            "(select count(1) from bis_zhsq t1, bis_zhsq_history t2 where t1.bis_zhsq_id=t2.zhsq_id and t2.type='02' and (?1 IS NULL OR ?1 = '' OR t1.BIS_SBGS_ID=?1 ) and t1.bis_zhsq_zhlb='03') pgrc_cnt, " +
            // 评估费
            "(select sum(AKC264) from bis_zhsq t1, BIS_JZDJ t2, kc24 t3 where t1.bis_zhsq_id=t2.bis_jzdj_zhsq_id and t2.bis_jzdj_ryjbbm='ZH00004' and t2.bis_jzdj_lsh=t3.AKC190 and (?1 IS NULL OR ?1 = '' OR t1.BIS_SBGS_ID=?1 )) pgfy_cnt, " +
            // 申请成功
            "(select count(1) from bis_zhsq t1, bis_zhsq_history t2 where t1.bis_zhsq_id=t2.zhsq_id and t2.type='04' and t2.result='通过' and (?1 IS NULL OR ?1 = '' OR t1.BIS_SBGS_ID=?1 ) and t1.bis_zhsq_zhlb='03') sqcg_cnt, " +
            // 已公示
            "(select count(1) from bis_zhsq t1, bis_gs t2 where t1.bis_zhsq_id=t2.bis_gs_zhid and t1.bis_zhsq_zhlb='03') ygs_cnt, " +
            // 已签约
            "(select count(1) from bis_zhsq t1, BIS_QY t2 where t1.bis_zhsq_id=t2.bis_qy_zhsq_id and t2.bis_gsgl_iszf='1' and t1.bis_zhsq_zhlb = '03'  and t1.bis_zhsq_zt in ('13', '90', '91')  and (?1 IS NULL OR ?1 = '' OR t2.BIS_QY_HLJG_ID=?1 )) yqy_cnt, " +
            // 已服务
            //"(select count(1) from (select * from (select b.*, (row_number() over(partition by bis_order_zhid order by bis_order_zhid desc)) px from bis_order b, bis_zhsq z where b.bis_order_zhid=z.bis_zhsq_id and (?2 IS NULL OR ?2 = '' OR z.bis_zhsq_hljg_bm=?2 )) t where t.px=1)) yfw_cnt, " +
            "( select  count(distinct t.bis_zhsq_snry_sfz) from bis_zhsq t   left join bis_order t3 on t.bis_zhsq_id = t3.bis_order_zhid   where t.bis_zhsq_zhlb = '03' and t.bis_zhsq_zt in ('13', '90', '91')  and t3.bis_order_zt in ('1', '3', '2', '9') ) yfw_cnt," +
            // 服务人次
            "(select count(1) from bis_order b, bis_zhsq z where b.bis_order_zhid=z.bis_zhsq_id and z.bis_zhsq_zt in ('13', '90', '91')  and b.bis_order_zt in ('1', '3', '2', '9') and (?2 IS NULL OR ?2 = '' OR z.bis_zhsq_hljg_bm=?2 )) fwrc_cnt " +
            "from dual ",nativeQuery = true)
    Object queryGk(String gsid, String ylddbm);

    /**
     * 查询首页结算
     * @param gsid /
     * @return /
     */
    @Query(value = "select substr(AAE002, 0 ,4),CJT100, sum(AKB068)  " +
            "from kb03 t where aae016='14' " +
            "group by substr(AAE002, 0 ,4),CJT100 " +
            "order by substr(AAE002, 0 ,4),CJT100 " ,nativeQuery = true)
    List<Object> querySettle(String gsid);

    /**
     * 查询首页结算
     * @param gsid /
     * @return /
     */
    @Query(value = "select count(t.bis_zhsq_id) cnt, t2.value,t2.label from BIS_ZHSQ t, sys_dict_detail t2 " +
            "where t.bis_zhsq_hldz_ssqx(+)=t2.value " +
            "and t2.dict_id='251' " +
            "group by t2.value,t2.label " +
            "order by t2.value " ,nativeQuery = true)
    List<Object> queryArea(String gsid);

    @Query(value = "select new me.zhengjie.business.domain.vo.ZhgkxmVo( " +
            "t.bisZhsqId, " +
            "g.bisGsglQymc as bisHljgQymc, " +
            "s.bisGsglQymc as bisSbgsQymc, " +
            "t.bisZhsqSnryId, " +
            "t.bisZhsqSnryXm, " +
            "t.bisZhsqSnryXb, " +
            "t.bisZhsqSnrySfz, " +
            "t.bisZhsqSnryYbssqx, " +
            "t.bisZhsqDykkrq, " +
            "t.bisZhsqDyjsrq, " +
            "t.bisZhsqXsdyq, " +
            "t.bisZhsqAdl, " +
            "t.bisZhsqZt, " +
            "t.bisZhsqAdlPc, " +
            "t.bisZhsqSndj, " +
            "t.bisZhsqRybh, " +
            "t.bisZhsqNl, " +
            "t.bisZhsqLxdh, " +
            "t.bisZhsqHldzSsqx, " +
            "t.bisZhsqHldzXxdz, " +
            "t.bisZhsqHldz, " +
            "t.bisZhsqJhrxm, " +
            "t.bisZhsqJhrdh, " +
            "t.bisZhsqGx, " +
            "t.bisZhsqSqyy, " +
            "t.bisZhsqYbkh, " +
            "t.bisZhsqCbzt, " +
            "t.bisUserId, " +
            "t.bisSbgsId, " +
            "t.bisPggsId, " +
            "t.bisZhsqGrbh, " +
            "t.bisZhsqZhlb, " +
            "t.bisZhsqHljgBm, " +
            "t.lrr, " +
            "t.lrrxm, " +
            "t.lrbm, " +
            "t.lrbmmc, " +
            "t.lrsj, " +
            "t.bz) " +
            "from Zhsq t " +
            "left join Gsgl g on t.bisZhsqHljgBm=g.bisGsglYljgdm " +
            "inner join Gsgl s on t.bisSbgsId=s.bisGsglId " +
            " where 1=1 " +
            " and (?1 IS NULL OR ?1 = '' OR t.bisZhsqSnryXm like '%'||?1||'%' OR t.bisZhsqSnrySfz like '%'||?1||'%' OR t.bisZhsqGrbh like '%'||?1||'%' OR t.bisZhsqLxdh like '%'||?1||'%' OR t.bisZhsqJhrdh like '%'||?1||'%' ) " +
            " and (?2 IS NULL OR ?2 = '' OR t.bisZhsqHldzSsqx = ?2 ) " +
            " and (?3 IS NULL OR ?3 = '' OR t.bisZhsqHljgBm = ?3 ) " +
            " and (?4 IS NULL OR ?4 = '' OR t.bisSbgsId = ?4 ) ")
    Page<ZhgkxmVo> queryYsq(String blurry, String area, String ylddbm, String gsid, Pageable pageable);



    @Query(value = "select new me.zhengjie.business.domain.vo.ZhgkxmVo( " +
            "t.bisZhsqId, " +
            "g.bisGsglQymc as bisHljgQymc, " +
            "s.bisGsglQymc as bisSbgsQymc, " +
            "t.bisZhsqSnryId, " +
            "t.bisZhsqSnryXm, " +
            "t.bisZhsqSnryXb, " +
            "t.bisZhsqSnrySfz, " +
            "t.bisZhsqSnryYbssqx, " +
            "t.bisZhsqDykkrq, " +
            "t.bisZhsqDyjsrq, " +
            "t.bisZhsqXsdyq, " +
            "t.bisZhsqAdl, " +
            "t.bisZhsqZt, " +
            "t.bisZhsqAdlPc, " +
            "t.bisZhsqSndj, " +
            "t.bisZhsqRybh, " +
            "t.bisZhsqNl, " +
            "t.bisZhsqLxdh, " +
            "t.bisZhsqHldzSsqx, " +
            "t.bisZhsqHldzXxdz, " +
            "t.bisZhsqHldz, " +
            "t.bisZhsqJhrxm, " +
            "t.bisZhsqJhrdh, " +
            "t.bisZhsqGx, " +
            "t.bisZhsqSqyy, " +
            "t.bisZhsqYbkh, " +
            "t.bisZhsqCbzt, " +
            "t.bisUserId, " +
            "t.bisSbgsId, " +
            "t.bisPggsId, " +
            "t.bisZhsqGrbh, " +
            "t.bisZhsqZhlb, " +
            "t.bisZhsqHljgBm, " +
            "t.lrr, " +
            "t.lrrxm, " +
            "t.lrbm, " +
            "t.lrbmmc, " +
            "t.lrsj, " +
            "t.bz) " +
            "from Zhsq t " +
            "left join Gsgl g on t.bisZhsqHljgBm=g.bisGsglYljgdm " +
            "inner join Gsgl s on t.bisSbgsId=s.bisGsglId " +
            "inner join ZhsqHistory t2 on t.bisZhsqId=t2.zhsqId and t2.type='02' and t2.result='通过' " +
            " where 1=1 " +
            " and (?1 IS NULL OR ?1 = '' OR t.bisZhsqSnryXm like '%'||?1||'%' OR t.bisZhsqSnrySfz like '%'||?1||'%' OR t.bisZhsqGrbh like '%'||?1||'%' OR t.bisZhsqLxdh like '%'||?1||'%' OR t.bisZhsqJhrdh like '%'||?1||'%' ) " +
            " and (?2 IS NULL OR ?2 = '' OR t.bisZhsqHldzSsqx = ?2 ) " +
            " and (?3 IS NULL OR ?3 = '' OR t.bisZhsqHljgBm = ?3 ) " ,
            countQuery = "select count(*) from Zhsq t " +
                    "left join Gsgl g on t.bisZhsqHljgBm=g.bisGsglYljgdm " +
                    "inner join Gsgl s on t.bisSbgsId=s.bisGsglId " +
                    "inner join ZhsqHistory t2 on t.bisZhsqId=t2.zhsqId and t2.type='02' and t2.result='通过' " +
                    " where 1=1 " +
                    " and (?1 IS NULL OR ?1 = '' OR t.bisZhsqSnryXm like '%'||?1||'%' OR t.bisZhsqSnrySfz like '%'||?1||'%' OR t.bisZhsqGrbh like '%'||?1||'%' OR t.bisZhsqLxdh like '%'||?1||'%' OR t.bisZhsqJhrdh like '%'||?1||'%' ) " +
                    " and (?2 IS NULL OR ?2 = '' OR t.bisZhsqHldzSsqx = ?2 ) " +
                    " and (?3 IS NULL OR ?3 = '' OR t.bisZhsqHljgBm = ?3 ) ")
    Page<ZhgkxmVo> queryPgcg(String blurry, String area, String ylddbm, Pageable pageable);


    @Query(value = "select new me.zhengjie.business.domain.vo.ZhgkPgrcVo( " +
            "t.bisZhsqId, " +
            "g.bisGsglQymc as bisHljgQymc, " +
            "s.bisGsglQymc as bisSbgsQymc, " +
            "t.bisZhsqSnryId, " +
            "t.bisZhsqSnryXm, " +
            "t.bisZhsqSnryXb, " +
            "t.bisZhsqSnrySfz, " +
            "t.bisZhsqSnryYbssqx, " +
            "t.bisZhsqDykkrq, " +
            "t.bisZhsqDyjsrq, " +
            "t.bisZhsqXsdyq, " +
            "t.bisZhsqAdl, " +
            "t.bisZhsqZt, " +
            "t.bisZhsqAdlPc, " +
            "t.bisZhsqSndj, " +
            "t.bisZhsqRybh, " +
            "t.bisZhsqNl, " +
            "t.bisZhsqLxdh, " +
            "t.bisZhsqHldzSsqx, " +
            "t.bisZhsqHldzXxdz, " +
            "t.bisZhsqHldz, " +
            "t.bisZhsqJhrxm, " +
            "t.bisZhsqJhrdh, " +
            "t.bisZhsqGx, " +
            "t.bisZhsqSqyy, " +
            "t.bisZhsqYbkh, " +
            "t.bisZhsqCbzt, " +
            "t.bisUserId, " +
            "t.bisSbgsId, " +
            "t.bisPggsId, " +
            "t.bisZhsqGrbh, " +
            "t.bisZhsqZhlb, " +
            "t.bisZhsqHljgBm, " +
            "t.lrr, " +
            "t.lrrxm, " +
            "t.lrbm, " +
            "t.lrbmmc, " +
            "t.lrsj, " +
            "t.bz," +
            "t2.result AS pgResult," +
            "t2.lrsj AS pgTime," +
            "t2.lrrxm AS pgr) " +
            "from Zhsq t " +
            "left join Gsgl g on t.bisZhsqHljgBm=g.bisGsglYljgdm " +
            "inner join Gsgl s on t.bisSbgsId=s.bisGsglId " +
            "inner join ZhsqHistory t2 on t.bisZhsqId=t2.zhsqId and t2.type='02' " +
            " where 1=1 " +
            " and (?1 IS NULL OR ?1 = '' OR t.bisZhsqSnryXm like '%'||?1||'%' OR t.bisZhsqSnrySfz like '%'||?1||'%' OR t.bisZhsqGrbh like '%'||?1||'%' OR t.bisZhsqLxdh like '%'||?1||'%' OR t.bisZhsqJhrdh like '%'||?1||'%' ) " +
            " and (?2 IS NULL OR ?2 = '' OR t.bisZhsqHldzSsqx = ?2 ) " +
            " and (?3 IS NULL OR ?3 = '' OR t.bisZhsqHljgBm = ?3 ) " +
            " and (?4 IS NULL OR ?4 = '' OR t2.result = ?4 ) " )
    Page<ZhgkPgrcVo> queryPgrc(String blurry, String area, String ylddbm, String sftg, Pageable pageable);


    @Query(value = "select new me.zhengjie.business.domain.vo.ZhgkSqcgVo( " +
            "t.bisZhsqId, " +
            "g.bisGsglQymc as bisHljgQymc, " +
            "s.bisGsglQymc as bisSbgsQymc, " +
            "t.bisZhsqSnryId, " +
            "t.bisZhsqSnryXm, " +
            "t.bisZhsqSnryXb, " +
            "t.bisZhsqSnrySfz, " +
            "t.bisZhsqSnryYbssqx, " +
            "t.bisZhsqDykkrq, " +
            "t.bisZhsqDyjsrq, " +
            "t.bisZhsqXsdyq, " +
            "t.bisZhsqAdl, " +
            "t.bisZhsqZt, " +
            "t.bisZhsqAdlPc, " +
            "t.bisZhsqSndj, " +
            "t.bisZhsqRybh, " +
            "t.bisZhsqNl, " +
            "t.bisZhsqLxdh, " +
            "t.bisZhsqHldzSsqx, " +
            "t.bisZhsqHldzXxdz, " +
            "t.bisZhsqHldz, " +
            "t.bisZhsqJhrxm, " +
            "t.bisZhsqJhrdh, " +
            "t.bisZhsqGx, " +
            "t.bisZhsqSqyy, " +
            "t.bisZhsqYbkh, " +
            "t.bisZhsqCbzt, " +
            "t.bisUserId, " +
            "t.bisSbgsId, " +
            "t.bisPggsId, " +
            "t.bisZhsqGrbh, " +
            "t.bisZhsqZhlb, " +
            "t.bisZhsqHljgBm, " +
            "t.lrr, " +
            "t.lrrxm, " +
            "t.lrbm, " +
            "t.lrbmmc, " +
            "t.lrsj, " +
            "t.bz, " +
            "t2.result AS fhResult," +
            "t2.lrsj AS fhTime," +
            "t2.lrrxm AS fhr) " +
            "from Zhsq t " +
            "left join Gsgl g on t.bisZhsqHljgBm=g.bisGsglYljgdm " +
            "inner join Gsgl s on t.bisSbgsId=s.bisGsglId " +
            "inner join ZhsqHistory t2 on t.bisZhsqId=t2.zhsqId and t2.type='04' and t2.result='通过' " +
            " where 1=1 " +
            " and (?1 IS NULL OR ?1 = '' OR t.bisZhsqSnryXm like '%'||?1||'%' OR t.bisZhsqSnrySfz like '%'||?1||'%' OR t.bisZhsqGrbh like '%'||?1||'%' OR t.bisZhsqLxdh like '%'||?1||'%' OR t.bisZhsqJhrdh like '%'||?1||'%' ) " +
            " and (?2 IS NULL OR ?2 = '' OR t.bisZhsqHldzSsqx = ?2 ) " +
            " and (?3 IS NULL OR ?3 = '' OR t.bisZhsqHljgBm = ?3 ) ")
    Page<ZhgkSqcgVo> querySqcg(String blurry, String area, String ylddbm, Pageable pageable);


    @Query(value = "select new me.zhengjie.business.domain.vo.ZhgkGsVo( " +
            "t.bisZhsqId, " +
            "g.bisGsglQymc as bisHljgQymc, " +
            "s.bisGsglQymc as bisSbgsQymc, " +
            "t.bisZhsqSnryId, " +
            "t.bisZhsqSnryXm, " +
            "t.bisZhsqSnryXb, " +
            "t.bisZhsqSnrySfz, " +
            "t.bisZhsqSnryYbssqx, " +
            "t.bisZhsqDykkrq, " +
            "t.bisZhsqDyjsrq, " +
            "t.bisZhsqXsdyq, " +
            "t.bisZhsqAdl, " +
            "t.bisZhsqZt, " +
            "t.bisZhsqAdlPc, " +
            "t.bisZhsqSndj, " +
            "t.bisZhsqRybh, " +
            "t.bisZhsqNl, " +
            "t.bisZhsqLxdh, " +
            "t.bisZhsqHldzSsqx, " +
            "t.bisZhsqHldzXxdz, " +
            "t.bisZhsqHldz, " +
            "t.bisZhsqJhrxm, " +
            "t.bisZhsqJhrdh, " +
            "t.bisZhsqGx, " +
            "t.bisZhsqSqyy, " +
            "t.bisZhsqYbkh, " +
            "t.bisZhsqCbzt, " +
            "t.bisUserId, " +
            "t.bisSbgsId, " +
            "t.bisPggsId, " +
            "t.bisZhsqGrbh, " +
            "t.bisZhsqZhlb, " +
            "t.bisZhsqHljgBm, " +
            "t.lrr, " +
            "t.lrrxm, " +
            "t.lrbm, " +
            "t.lrbmmc, " +
            "t.lrsj, " +
            "t.bz," +
            "t2.bisGsRq," +
            "t2.bisGsZt) " +
            "from Zhsq t " +
            "left join Gsgl g on t.bisZhsqHljgBm=g.bisGsglYljgdm " +
            "inner join Gsgl s on t.bisSbgsId=s.bisGsglId " +
            "inner join Gs t2 on t.bisZhsqId=t2.bisGsZhid " +
            " where 1=1 " +
            " and (?1 IS NULL OR ?1 = '' OR t.bisZhsqSnryXm like '%'||?1||'%' OR t.bisZhsqSnrySfz like '%'||?1||'%' OR t.bisZhsqGrbh like '%'||?1||'%' OR t.bisZhsqLxdh like '%'||?1||'%' OR t.bisZhsqJhrdh like '%'||?1||'%' ) " +
            " and (?2 IS NULL OR ?2 = '' OR t.bisZhsqHldzSsqx = ?2 ) " +
            " and (?3 IS NULL OR ?3 = '' OR t.bisZhsqHljgBm = ?3 ) " +
            " and (?4 IS NULL OR ?4 = '' OR t2.bisGsZt = ?4 ) ")
    Page<ZhgkGsVo> queryYgs(String blurry, String area, String ylddbm, String gszt, Pageable pageable);


    @Query(value = "select new me.zhengjie.business.domain.vo.ZhgkQyVo( " +
            "t.bisZhsqId, " +
            "g.bisGsglQymc as bisHljgQymc, " +
            "s.bisGsglQymc as bisSbgsQymc, " +
            "t.bisZhsqSnryId, " +
            "t.bisZhsqSnryXm, " +
            "t.bisZhsqSnryXb, " +
            "t.bisZhsqSnrySfz, " +
            "t.bisZhsqSnryYbssqx, " +
            "t.bisZhsqDykkrq, " +
            "t.bisZhsqDyjsrq, " +
            "t.bisZhsqXsdyq, " +
            "t.bisZhsqAdl, " +
            "t.bisZhsqZt, " +
            "t.bisZhsqAdlPc, " +
            "t.bisZhsqSndj, " +
            "t.bisZhsqRybh, " +
            "t.bisZhsqNl, " +
            "t.bisZhsqLxdh, " +
            "t.bisZhsqHldzSsqx, " +
            "t.bisZhsqHldzXxdz, " +
            "t.bisZhsqHldz, " +
            "t.bisZhsqJhrxm, " +
            "t.bisZhsqJhrdh, " +
            "t.bisZhsqGx, " +
            "t.bisZhsqSqyy, " +
            "t.bisZhsqYbkh, " +
            "t.bisZhsqCbzt, " +
            "t.bisUserId, " +
            "t.bisSbgsId, " +
            "t.bisPggsId, " +
            "t.bisZhsqGrbh, " +
            "t.bisZhsqZhlb, " +
            "t.bisZhsqHljgBm, " +
            "t.lrr, " +
            "t.lrrxm, " +
            "t.lrbm, " +
            "t.lrbmmc, " +
            "t.lrsj, " +
            "t.bz," +
            "t2.bisQyQysj," +
            "t2.bisQyZt) " +
            "from Zhsq t " +
            "left join Gsgl g on t.bisZhsqHljgBm=g.bisGsglYljgdm " +
            "inner join Gsgl s on t.bisSbgsId=s.bisGsglId " +
            "inner join Qy t2 on t.bisZhsqId=t2.bisQyZhsqId and t2.bisGsglIszf='1' " +
            " where 1=1 " +
            " and (?1 IS NULL OR ?1 = '' OR t.bisZhsqSnryXm like '%'||?1||'%' OR t.bisZhsqSnrySfz like '%'||?1||'%' OR t.bisZhsqGrbh like '%'||?1||'%' OR t.bisZhsqLxdh like '%'||?1||'%' OR t.bisZhsqJhrdh like '%'||?1||'%' ) " +
            " and (?2 IS NULL OR ?2 = '' OR t.bisZhsqHldzSsqx = ?2 ) " +
            " and (?3 IS NULL OR ?3 = '' OR t.bisZhsqHljgBm = ?3 ) " +
            " and (?4 IS NULL OR ?4 = '' OR t2.bisQyZt = ?4 ) ")
    Page<ZhgkQyVo> queryYqy(String blurry, String area, String ylddbm, String qyzt, Pageable pageable);




    @Query(value = "select new me.zhengjie.business.domain.vo.ZhgkPgfymxVo( " +
            "t.bisZhsqId, " +
            "g.bisGsglQymc as bisHljgQymc, " +
            "s.bisGsglQymc as bisSbgsQymc, " +
            "t.bisZhsqSnryId, " +
            "t.bisZhsqSnryXm, " +
            "t.bisZhsqSnryXb, " +
            "t.bisZhsqSnrySfz, " +
            "t.bisZhsqSnryYbssqx, " +
            "t.bisZhsqDykkrq, " +
            "t.bisZhsqDyjsrq, " +
            "t.bisZhsqXsdyq, " +
            "t.bisZhsqAdl, " +
            "t.bisZhsqZt, " +
            "t.bisZhsqAdlPc, " +
            "t.bisZhsqSndj, " +
            "t.bisZhsqRybh, " +
            "t.bisZhsqNl, " +
            "t.bisZhsqLxdh, " +
            "t.bisZhsqHldzSsqx, " +
            "t.bisZhsqHldzXxdz, " +
            "t.bisZhsqHldz, " +
            "t.bisZhsqJhrxm, " +
            "t.bisZhsqJhrdh, " +
            "t.bisZhsqGx, " +
            "t.bisZhsqSqyy, " +
            "t.bisZhsqYbkh, " +
            "t.bisZhsqCbzt, " +
            "t.bisUserId, " +
            "t.bisSbgsId, " +
            "t.bisPggsId, " +
            "t.bisZhsqGrbh, " +
            "t.bisZhsqZhlb, " +
            "t.bisZhsqHljgBm, " +
            "t.lrr, " +
            "t.lrrxm, " +
            "t.lrbm, " +
            "t.lrbmmc, " +
            "t.lrsj, " +
            "t.bz," +
            "t3.akc021," +
            "t3.aae036," +
            "t3.akc264," +
            "t3.akc261," +
            "t3.akc255," +
            "t3.akc260," +
            "t3.zkc024," +
            "t3.zkc025," +
            "t3.ckc282," +
            "t3.akc190," +
            "t3.aae072) " +
            "from Zhsq t " +
            "left join Gsgl g on t.bisZhsqHljgBm=g.bisGsglYljgdm " +
            "inner join Gsgl s on t.bisSbgsId=s.bisGsglId " +
            "inner join Jzdj t2 on t.bisZhsqId = t2.bisJzdjZhsqId and t2.bisJzdjRyjbbm = 'ZH00004' " +
            "inner join KC24 t3 on t2.bisJzdjLsh = t3.akc190 " +
            " where 1=1 " +
            " and (?1 IS NULL OR ?1 = '' OR t.bisZhsqSnryXm like '%'||?1||'%' OR t.bisZhsqSnrySfz like '%'||?1||'%' OR t.bisZhsqGrbh like '%'||?1||'%' OR t.bisZhsqLxdh like '%'||?1||'%' OR t.bisZhsqJhrdh like '%'||?1||'%' ) " +
            " and (?2 IS NULL OR ?2 = '' OR t.bisZhsqHldzSsqx = ?2 ) " +
            " and (?3 IS NULL OR ?3 = '' OR t.bisZhsqHljgBm = ?3 ) ")
    Page<ZhgkPgfymxVo> queryPgfymx(String blurry, String area, String ylddbm, String qyzt, Pageable pageable);




    @Query(value = "select new me.zhengjie.business.domain.vo.ZhgkxmVo( " +
            "t.bisZhsqId, " +
            "g.bisGsglQymc as bisHljgQymc, " +
            "s.bisGsglQymc as bisSbgsQymc, " +
            "t.bisZhsqSnryId, " +
            "t.bisZhsqSnryXm, " +
            "t.bisZhsqSnryXb, " +
            "t.bisZhsqSnrySfz, " +
            "t.bisZhsqSnryYbssqx, " +
            "t.bisZhsqDykkrq, " +
            "t.bisZhsqDyjsrq, " +
            "t.bisZhsqXsdyq, " +
            "t.bisZhsqAdl, " +
            "t.bisZhsqZt, " +
            "t.bisZhsqAdlPc, " +
            "t.bisZhsqSndj, " +
            "t.bisZhsqRybh, " +
            "t.bisZhsqNl, " +
            "t.bisZhsqLxdh, " +
            "t.bisZhsqHldzSsqx, " +
            "t.bisZhsqHldzXxdz, " +
            "t.bisZhsqHldz, " +
            "t.bisZhsqJhrxm, " +
            "t.bisZhsqJhrdh, " +
            "t.bisZhsqGx, " +
            "t.bisZhsqSqyy, " +
            "t.bisZhsqYbkh, " +
            "t.bisZhsqCbzt, " +
            "t.bisUserId, " +
            "t.bisSbgsId, " +
            "t.bisPggsId, " +
            "t.bisZhsqGrbh, " +
            "t.bisZhsqZhlb, " +
            "t.bisZhsqHljgBm, " +
            "t.lrr, " +
            "t.lrrxm, " +
            "t.lrbm, " +
            "t.lrbmmc, " +
            "t.lrsj, " +
            "t.bz) " +
            "from Zhsq t " +
            "left join Gsgl g on t.bisZhsqHljgBm=g.bisGsglYljgdm " +
            "inner join Gsgl s on t.bisSbgsId=s.bisGsglId " +
            " where 1=1 " +
            " and t.bisZhsqId in (select o.bisOrderZhid from Order o) " +
            " and (?1 IS NULL OR ?1 = '' OR t.bisZhsqSnryXm like '%'||?1||'%' OR t.bisZhsqSnrySfz like '%'||?1||'%' OR t.bisZhsqGrbh like '%'||?1||'%' OR t.bisZhsqLxdh like '%'||?1||'%' OR t.bisZhsqJhrdh like '%'||?1||'%' ) " +
            " and (?2 IS NULL OR ?2 = '' OR t.bisZhsqHldzSsqx = ?2 ) " +
            " and (?3 IS NULL OR ?3 = '' OR t.bisZhsqHljgBm = ?3 ) ")
    Page<ZhgkxmVo> queryYfw(String blurry, String area, String ylddbm, Pageable pageable);

    @Query(value = "select new me.zhengjie.business.domain.vo.ZhgkFwrcVo( " +
            "t.bisZhsqId, " +
            "g.bisGsglQymc as bisHljgQymc, " +
            "s.bisGsglQymc as bisSbgsQymc, " +
            "t.bisZhsqSnryId, " +
            "t.bisZhsqSnryXm, " +
            "t.bisZhsqSnryXb, " +
            "t.bisZhsqSnrySfz, " +
            "t.bisZhsqSnryYbssqx, " +
            "t.bisZhsqDykkrq, " +
            "t.bisZhsqDyjsrq, " +
            "t.bisZhsqXsdyq, " +
            "t.bisZhsqAdl, " +
            "t.bisZhsqZt, " +
            "t.bisZhsqAdlPc, " +
            "t.bisZhsqSndj, " +
            "t.bisZhsqRybh, " +
            "t.bisZhsqNl, " +
            "t.bisZhsqLxdh, " +
            "t.bisZhsqHldzSsqx, " +
            "t.bisZhsqHldzXxdz, " +
            "t.bisZhsqHldz, " +
            "t.bisZhsqJhrxm, " +
            "t.bisZhsqJhrdh, " +
            "t.bisZhsqGx, " +
            "t.bisZhsqSqyy, " +
            "t.bisZhsqYbkh, " +
            "t.bisZhsqCbzt, " +
            "t.bisUserId, " +
            "t.bisSbgsId, " +
            "t.bisPggsId, " +
            "t.bisZhsqGrbh, " +
            "t.bisZhsqZhlb, " +
            "t.bisZhsqHljgBm, " +
            "t.lrr, " +
            "t.lrrxm, " +
            "t.lrbm, " +
            "t.lrbmmc, " +
            "t.lrsj, " +
            "t.bz," +
            "t2.bisOrderHlrq," +
            "t2.bisOrderHlyName," +
            "t2.bisOrderStartTime," +
            "t2.bisOrderEndTime," +
            "t2.bisOrderHlstart," +
            "t2.bisOrderHlend," +
            "t2.bisOrderZt) " +
            "from Zhsq t " +
            "left join Gsgl g on t.bisZhsqHljgBm=g.bisGsglYljgdm " +
            "inner join Gsgl s on t.bisSbgsId=s.bisGsglId " +
            "inner join Order t2 on t.bisZhsqId=t2.bisOrderZhid " +
            " where 1=1 " +
            " and (?1 IS NULL OR ?1 = '' OR t.bisZhsqSnryXm like '%'||?1||'%' OR t.bisZhsqSnrySfz like '%'||?1||'%' OR t.bisZhsqGrbh like '%'||?1||'%' OR t.bisZhsqLxdh like '%'||?1||'%' OR t.bisZhsqJhrdh like '%'||?1||'%' ) " +
            " and (?2 IS NULL OR ?2 = '' OR t.bisZhsqHldzSsqx = ?2 ) " +
            " and (?3 IS NULL OR ?3 = '' OR t.bisZhsqHljgBm = ?3 ) " +
            " and (?4 IS NULL OR ?4 = '' OR t2.bisOrderZt = ?4 ) " )
    Page<ZhgkFwrcVo> queryFwrc(String blurry, String area, String ylddbm, String orderZt, Pageable pageable);


    @Query(value = "select new me.zhengjie.business.domain.vo.ZhgkxmVo( " +
            "t.bisZhsqId, " +
            "g.bisGsglQymc as bisHljgQymc, " +
            "s.bisGsglQymc as bisSbgsQymc, " +
            "t.bisZhsqSnryId, " +
            "t.bisZhsqSnryXm, " +
            "t.bisZhsqSnryXb, " +
            "t.bisZhsqSnrySfz, " +
            "t.bisZhsqSnryYbssqx, " +
            "t.bisZhsqDykkrq, " +
            "t.bisZhsqDyjsrq, " +
            "t.bisZhsqXsdyq, " +
            "t.bisZhsqAdl, " +
            "t.bisZhsqZt, " +
            "t.bisZhsqAdlPc, " +
            "t.bisZhsqSndj, " +
            "t.bisZhsqRybh, " +
            "t.bisZhsqNl, " +
            "t.bisZhsqLxdh, " +
            "t.bisZhsqHldzSsqx, " +
            "t.bisZhsqHldzXxdz, " +
            "t.bisZhsqHldz, " +
            "t.bisZhsqJhrxm, " +
            "t.bisZhsqJhrdh, " +
            "t.bisZhsqGx, " +
            "t.bisZhsqSqyy, " +
            "t.bisZhsqYbkh, " +
            "t.bisZhsqCbzt, " +
            "t.bisUserId, " +
            "t.bisSbgsId, " +
            "t.bisPggsId, " +
            "t.bisZhsqGrbh, " +
            "t.bisZhsqZhlb, " +
            "t.bisZhsqHljgBm, " +
            "t.lrr, " +
            "t.lrrxm, " +
            "t.lrbm, " +
            "t.lrbmmc, " +
            "t.lrsj, " +
            "t.bz) " +
            "from Zhsq t " +
            "left join Gsgl g on t.bisZhsqHljgBm=g.bisGsglYljgdm " +
            "inner join Gsgl s on t.bisSbgsId=s.bisGsglId " +
            " where 1=1 " +
            " and (?1 IS NULL OR ?1 = '' OR t.bisZhsqSnryXm like '%'||?1||'%' OR t.bisZhsqSnrySfz like '%'||?1||'%' OR t.bisZhsqGrbh like '%'||?1||'%' OR t.bisZhsqLxdh like '%'||?1||'%' OR t.bisZhsqJhrdh like '%'||?1||'%' ) " +
            " and (?2 IS NULL OR ?2 = '' OR t.bisZhsqHldzSsqx = ?2 ) " +
            " and (?3 IS NULL OR ?3 = '' OR t.bisZhsqHljgBm = ?3 ) " +
            " and t.bisUserId =?4 ")
    Page<ZhgkxmVo> queryBySqrid(String blurry, String area, String ylddbm, Long userId, Pageable pageable);



    @Query(value = "select new me.zhengjie.business.domain.vo.ZhgkZpbhVo( " +
            "t.bisZhsqId, " +
            "g.bisGsglQymc as bisHljgQymc, " +
            "s.bisGsglQymc as bisSbgsQymc, " +
            "t.bisZhsqSnryId, " +
            "t.bisZhsqSnryXm, " +
            "t.bisZhsqSnryXb, " +
            "t.bisZhsqSnrySfz, " +
            "t.bisZhsqSnryYbssqx, " +
            "t.bisZhsqDykkrq, " +
            "t.bisZhsqDyjsrq, " +
            "t.bisZhsqXsdyq, " +
            "t.bisZhsqAdl, " +
            "t.bisZhsqZt, " +
            "t.bisZhsqAdlPc, " +
            "t.bisZhsqSndj, " +
            "t.bisZhsqRybh, " +
            "t.bisZhsqNl, " +
            "t.bisZhsqLxdh, " +
            "t.bisZhsqHldzSsqx, " +
            "t.bisZhsqHldzXxdz, " +
            "t.bisZhsqHldz, " +
            "t.bisZhsqJhrxm, " +
            "t.bisZhsqJhrdh, " +
            "t.bisZhsqGx, " +
            "t.bisZhsqSqyy, " +
            "t.bisZhsqYbkh, " +
            "t.bisZhsqCbzt, " +
            "t.bisUserId, " +
            "t.bisSbgsId, " +
            "t.bisPggsId, " +
            "t.bisZhsqGrbh, " +
            "t.bisZhsqZhlb, " +
            "t.bisZhsqHljgBm, " +
            "t.lrr, " +
            "t.lrrxm, " +
            "t.lrbm, " +
            "t.lrbmmc, " +
            "t.lrsj, " +
            "t.bz, " +
            "t2.bz AS zpspYj," +
            "t2.lrsj AS zpspTime," +
            "t2.lrrxm AS zpspr) " +
            "from Zhsq t " +
            "left join Gsgl g on t.bisZhsqHljgBm=g.bisGsglYljgdm " +
            "inner join Gsgl s on t.bisSbgsId=s.bisGsglId " +
            "inner join ZhsqHistory t2 on t.bisZhsqId=t2.zhsqId and t2.type='11' and t2.result='未通过' " +
            " where 1=1 " +
            " and (?1 IS NULL OR ?1 = '' OR t.bisZhsqSnryXm like '%'||?1||'%' OR t.bisZhsqSnrySfz like '%'||?1||'%' OR t.bisZhsqGrbh like '%'||?1||'%' OR t.bisZhsqLxdh like '%'||?1||'%' OR t.bisZhsqJhrdh like '%'||?1||'%' ) " +
            " and (?2 IS NULL OR ?2 = '' OR t.bisZhsqHldzSsqx = ?2 ) " +
            " and (?3 IS NULL OR ?3 = '' OR t.bisSbgsId = ?3 ) "  )
    /**
     * 自评驳回列表
     */
    Page<ZhgkZpbhVo> queryZpbhList(String blurry, String area, String sbgsId, Pageable pageable);


    @Query(value = "select new me.zhengjie.business.domain.vo.ZhgkZpbhVo( " +
            "t.bisZhsqId, " +
            "g.bisGsglQymc as bisHljgQymc, " +
            "s.bisGsglQymc as bisSbgsQymc, " +
            "t.bisZhsqSnryId, " +
            "t.bisZhsqSnryXm, " +
            "t.bisZhsqSnryXb, " +
            "t.bisZhsqSnrySfz, " +
            "t.bisZhsqSnryYbssqx, " +
            "t.bisZhsqDykkrq, " +
            "t.bisZhsqDyjsrq, " +
            "t.bisZhsqXsdyq, " +
            "t.bisZhsqAdl, " +
            "t.bisZhsqZt, " +
            "t.bisZhsqAdlPc, " +
            "t.bisZhsqSndj, " +
            "t.bisZhsqRybh, " +
            "t.bisZhsqNl, " +
            "t.bisZhsqLxdh, " +
            "t.bisZhsqHldzSsqx, " +
            "t.bisZhsqHldzXxdz, " +
            "t.bisZhsqHldz, " +
            "t.bisZhsqJhrxm, " +
            "t.bisZhsqJhrdh, " +
            "t.bisZhsqGx, " +
            "t.bisZhsqSqyy, " +
            "t.bisZhsqYbkh, " +
            "t.bisZhsqCbzt, " +
            "t.bisUserId, " +
            "t.bisSbgsId, " +
            "t.bisPggsId, " +
            "t.bisZhsqGrbh, " +
            "t.bisZhsqZhlb, " +
            "t.bisZhsqHljgBm, " +
            "t.lrr, " +
            "t.lrrxm, " +
            "t.lrbm, " +
            "t.lrbmmc, " +
            "t.lrsj, " +
            "t.bz, " +
            "t2.bz AS zpspYj," +
            "t2.lrsj AS zpspTime," +
            "t2.lrrxm AS zpspr) " +
            "from Zhsq t " +
            "left join Gsgl g on t.bisZhsqHljgBm=g.bisGsglYljgdm " +
            "inner join Gsgl s on t.bisSbgsId=s.bisGsglId " +
            "inner join ZhsqHistory t2 on t.bisZhsqId=t2.zhsqId and t2.type='11' and t2.result='未通过' " +
            " where 1=1 " +
            " and (?1 IS NULL OR ?1 = '' OR t.bisZhsqSnryXm like '%'||?1||'%' OR t.bisZhsqSnrySfz like '%'||?1||'%' OR t.bisZhsqGrbh like '%'||?1||'%' OR t.bisZhsqLxdh like '%'||?1||'%' OR t.bisZhsqJhrdh like '%'||?1||'%' ) " +
            " and (?2 IS NULL OR ?2 = '' OR t.bisZhsqHldzSsqx = ?2 ) " +
            " and (?3 IS NULL OR ?3 = '' OR t.bisSbgsId = ?3 ) "  )
    /**
     * 自评驳回列表
     */
    List<ZhgkZpbhVo> queryZpbhList(String blurry, String area, String sbgsId);



    /**
     * 查询参保类型为空的
     * @return /
     */
    @Query(value = "select * from (select * from bis_zhsq t where t.bis_zhsq_cblx is null ) where rownum<=?1",nativeQuery = true)
    List<Zhsq> selectCblxIsNull(int cnt);
    /**
     * 根据个人编号查询医保中心人员信息
     * @return /
     */
    @Query(value = "select new me.zhengjie.business.domain.vo.PersonVo(" +
            "R1.aac001, " +
            "R1.aac003, " +
            "R1.aac002, " +
            "R1.aac008, " +
            "R1.akc021, " +
            "R1.cardno, " +
            "R1.aac031, " +
            "R1.aab034Code, " +
            "R1.aac008Code, " +
            "R1.akc021Code, " +
            "R1.aac031Code, " +
            "R1.aab034Name)" +
            "FROM Person R1 " +
            "WHERE 1=1 " +
            "and (R1.aac001 = ?1 or R1.aac002 = ?1 )")
    PersonVo selectByAac001(String aac001);
    /**
     * 更改参保类型
     * @param cblx 参保类型
     * @param zhid 照护ID
     */
    @Modifying
    @Query(value = "update bis_zhsq set bis_zhsq_cblx = ?1 where bis_zhsq_id = ?2",nativeQuery = true)
    void updateCblx(String cblx, Long zhid);

    /**
     * 查询医保卡号不一致的
     * @return /
     */
    @Query(value = "select t1.* from bis_zhsq t1, view_person t2 " +
            " where t1.bis_zhsq_ybkh != t2.CARDNO " +
            " and t1.bis_zhsq_grbh=t2.aac001 ",nativeQuery = true)
    List<Zhsq> selectYbkh();
    /**
     * 更改医保卡号
     * @param ybkh 医保卡号
     * @param zhid 照护ID
     */
    @Modifying
    @Query(value = "update bis_zhsq set bis_zhsq_ybkh = ?1 where bis_zhsq_id = ?2",nativeQuery = true)
    void updateYbkh(String ybkh, Long zhid);


    @Query(value = "select (select count(*)" +
            "          from bis_zhsq zhsq0_" +
            "         where zhsq0_.bis_zhsq_hljg_id = ?1" +
            "           and zhsq0_.bis_zhsq_zhlb = ?2" +
            "           and (zhsq0_.bis_zhsq_zt in('00', '01', '02', '03', '04', '54', '55'))) cnt_zhsq," +
            "       (select count(*)" +
            "          from bis_zhsq r1" +
            "         where 1 = 1" +
            "           and r1.bis_zhsq_hljg_bm = ?1" +
            "           and r1.bis_zhsq_zhlb = ?2" +
            "           and r1.bis_zhsq_zt = '05') cnt_zhsqfh," +
            "       (select count(*)" +
            "          from bis_zhsq r1" +
            "         where 1 = 1" +
            "           and r1.bis_zhsq_hljg_bm = ?1" +
            "           and r1.bis_zhsq_zhlb = ?2" +
            "           and r1.bis_zhsq_zt = '80') cnt_zhsqth," +
            "       (select count(*)" +
            "          from bis_zhsq r1" +
            "         where 1 = 1" +
            "           and r1.bis_zhsq_hljg_bm = ?1" +
            "           and r1.bis_zhsq_zhlb = ?2" +
            "           and r1.bis_zhsq_zt in ('-4', '-5')) cnt_zhsqbh," +
            "       (select count(*)" +
            "          from bis_zhsq r1" +
            "         where 1 = 1" +
            "           and r1.bis_zhsq_hljg_bm = ?1" +
            "           and r1.bis_zhsq_zhlb = ?2" +
            "           and r1.bis_zhsq_zt = '05'" +
            "           and r1.bis_zhsq_dyjsrq <= to_date(?3,'yyyy/mm/dd')" +
            "           and r1.bis_zhsq_dyjsrq >= to_date(?4,'yyyy/mm/dd')" +
            "           ) cnt_zhsqjjgq," +
            "       (select count(*)" +
            "          from bis_zhsq r1,bis_zhsq_history r2" +
            "         where 1 = 1 " +
            "            and r1.bis_zhsq_id = r2.zhsq_id" +
            "           and r1.bis_zhsq_hljg_bm = ?1" +
            "           and r1.bis_zhsq_zhlb = ?2" +
            "           and r1.bis_zhsq_zt = '05'" +
            "           and to_char(r2.lrsj,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd')" +
            "           and r2.result = '通过'" +
            "           and r2.type = '04') cnt_jrfh," +
            "       (select count(*)" +
            "          from bis_zhsq r1,bis_zhsq_history r2" +
            "         where 1 = 1 " +
            "            and r1.bis_zhsq_id = r2.zhsq_id" +
            "           and r1.bis_zhsq_hljg_bm = ?1" +
            "           and r1.bis_zhsq_zhlb = ?2" +
            "           and r1.bis_zhsq_zt = '80'" +
            "           and to_char(r2.lrsj,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd')" +
            "           and r2.result = '未通过'" +
            "           and r2.type = '02') cnt_jrth," +
            "       (select count(*)" +
            "          from bis_zhsq r1,bis_zhsq_history r2" +
            "         where 1 = 1 " +
            "            and r1.bis_zhsq_id = r2.zhsq_id" +
            "           and r1.bis_zhsq_hljg_bm = ?1" +
            "           and r1.bis_zhsq_zhlb = ?2" +
            "           and r1.bis_zhsq_zt in ('-4', '-5')" +
            "           and to_char(r2.lrsj,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd')" +
            "           and r2.result = '未通过'" +
            "           and r2.type = '02') cnt_jrbh" +
            "  from dual",nativeQuery = true)
    Map<String, String> querOrgPageList(String ddbm, String zhlb, String bisZhsqDyjsrq2, String bisZhsqDyjsrq1);

    @Modifying
    @Transactional
    @Query(value = "UPDATE bis_zhsq t1 SET t1.PAY_STATUS = :payStatus WHERE t1.BIS_ZHSQ_ID = :bisZhsqId", nativeQuery = true)
    void updatePayStatus(@Param("payStatus") String payStatus, @Param("bisZhsqId") Long bisZhsqId);

    @Query(value = "select r2.bis_adl_xm_px, r2.bis_adl_xm_name, r1.bis_adl_xmxx_name, r1.bis_adl_xmxx_fs " +
            "from BIS_ADL_ZHSQ r1, bis_adl_xm r2 " +
            "where r1.bis_zhsq_id = ?1 and r1.ywlx = 'RYLX02' " +
            "and r1.bis_adl_xm_id = r2.bis_adl_xm_id " +
            "order by r2.bis_adl_xm_px", nativeQuery = true)
    List<Map<String, Object>> getPdfScore(String zhsqId);
}
